#!/use/bin/python
# coding=utf-8
import datetime

import MySQLdb


def getConnection():
    conn = MySQLdb.connect(
        host='172.20.154.103',
        port=3307,
        user='test_dmp',
        passwd='DghHC3lFM1KzT3ZJ',
        db='adevice',
        charset='utf8',
    )
    cur = conn.cursor()
    return conn, cur

#这是一个接口
def updateAdeviceValue():
        try:
                conn, cur = getConnection()
                # 使用cursor()方法获取操作游标
                cur = conn.cursor()
                #派生自
                updateSql = """
                update adevice a  join adevice_config t on a.id = t.adevice_id and t.`key` ='tv_derive_from'  and t.del_flag = 1
                        set a.tv_derive_from_value = t.value""" ;
                cur.execute(updateSql)
                rowcount = cur.rowcount
                print("派生自 tv_derive_from_value 字段更新数量:%s"%rowcount)
                #UI分辨率
                updateSql = """
                update adevice a  join adevice_config t on a.id = t.adevice_id and t.`key` ='tv_ui_resolution'  and t.del_flag = 1
                        set a.tv_ui_resolution_value = t.value""" ;
                cur.execute(updateSql)
                rowcount = cur.rowcount
                print("UI分辨率 tv_ui_resolution_value 字段更新数量:%s"%rowcount)
                # ram大小
                updateSql = """
                update adevice a  join adevice_config t on a.id = t.adevice_id and t.`key` ='tv_storage_ram_size'  and t.del_flag = 1
                        set a.tv_storage_ram_size_value = t.value""";
                cur.execute(updateSql)
                rowcount = cur.rowcount
                print("ram大小 tv_storage_ram_size_value 字段更新数量:%s" % rowcount)
                # 屏幕类型
                updateSql = """
                update adevice a  join adevice_config t on a.id = t.adevice_id and t.`key` ='tv_screen_type'  and t.del_flag = 1
                        set a.tv_screen_type_value = t.value""";
                cur.execute(updateSql)
                rowcount = cur.rowcount
                print("屏幕类型 tv_screen_type_value 字段更新数量:%s" % rowcount)
                # 销售渠道
                updateSql = """
                update adevice a  join adevice_config t on a.id = t.adevice_id and t.`key` ='tv_distribution_channel'  and t.del_flag = 1
                        set a.tv_distribution_channel_value = t.value""";
                cur.execute(updateSql)
                rowcount = cur.rowcount
                print("销售渠道 tv_distribution_channel_value 字段更新数量:%s" % rowcount)
                # 屏幕类型
                updateSql = """
                update adevice a  join adevice_config t on a.id = t.adevice_id and t.`key` ='tv_screen_type'  and t.del_flag = 1
                        set a.tv_screen_type_value = t.value""";
                cur.execute(updateSql)
                rowcount = cur.rowcount
                print("屏幕类型 tv_screen_type_value 字段更新数量:%s" % rowcount)
                # 屏幕尺寸
                updateSql = """
                update adevice a  join(
                        select group_concat(t.`value` separator ',') value,t.adevice_id from adevice_config t where t.`key` ='tv_screen_size' and t.del_flag = 1 group by t.adevice_id
                        ) tt on a.id = tt.adevice_id set tv_screen_size_value = tt.value""";
                cur.execute(updateSql)
                rowcount = cur.rowcount
                print("屏幕尺寸 tv_screen_size_value 字段更新数量:%s" % rowcount)
                # 软件信息:内容源支持项
                updateSql = """
                update adevice a  join(
                        select group_concat(t.`value` separator ',') value,t.adevice_id from adevice_config t where t.`key` ='tv_content_source_support' and t.del_flag = 1 group by t.adevice_id
                        ) tt on a.id = tt.adevice_id set tv_content_source_support_value = tt.value""";
                cur.execute(updateSql)
                rowcount = cur.rowcount
                print("软件信息:内容源支持项 tv_content_source_support_value 字段更新数量:%s" % rowcount)
                # 屏幕分辨率
                updateSql = """
                update adevice a  join(
                        select group_concat(t.`value` separator ',') value,t.adevice_id from adevice_config t where t.`key` ='tv_screen_resolution' and t.del_flag = 1 group by t.adevice_id
                        ) tt on a.id = tt.adevice_id set tv_screen_resolution_value = tt.value""";
                cur.execute(updateSql)
                rowcount = cur.rowcount
                print("屏幕分辨率 tv_screen_resolution_value 字段更新数量:%s" % rowcount)
                conn.commit()
        except Exception as e:
                print(e)
        finally:
                cur.close()
                conn.close()

if __name__ == '__main__':
    updateAdeviceValue()
